package cn.aloxc.tianya.reader;

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;

public class Activity_test_SqlLite extends Activity{
	DbHelper mOpenHelper = null;
	Button delete = null;
	Button insert = null;
	Button drop = null;
	Button create = null;
	Button counter = null;
	OnClickListener listener_delete = null;
	OnClickListener listener_insert = null;
	OnClickListener listener_drop = null;
	OnClickListener listener_create = null;
	OnClickListener listener_counter = null;
	private static final String DATABASE_NAME = "tianyaReader.db";
	private static final int DATABASE_VERSION = 1;
	private static final String TABLE_NAME = "tb_data";
	private static final String TITLE = "title";
	private static final String BODY = "body";
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		this.setContentView(R.layout.activity_test_sqlite);
		mOpenHelper = new DbHelper(this);
		this.initLayoutAndBind();
	}
	
	private void initLayoutAndBind(){
		
		listener_create = new OnClickListener(){
			public void onClick(View v) {
				Activity_test_SqlLite.this.createTable();
			}
		};
		listener_delete = new OnClickListener(){
			public void onClick(View v) {
				Activity_test_SqlLite.this.deleteData();
			}
		};
		listener_insert = new OnClickListener(){
			public void onClick(View v) {
				Activity_test_SqlLite.this.insertData();
			}
		};
		listener_drop = new OnClickListener(){
			public void onClick(View v) {
				Activity_test_SqlLite.this.dropTable();
			}
		};
		listener_counter = new OnClickListener(){
			public void onClick(View v) {
				Activity_test_SqlLite.this.showItems();
			}
		};
		create = (Button)this.findViewById(R.id.test_sqlite_create);
		insert = (Button)this.findViewById(R.id.test_sqlite_insert);
		drop = (Button)this.findViewById(R.id.test_sqlite_drop);
		delete = (Button)this.findViewById(R.id.test_sqlite_delete);
		counter = (Button)this.findViewById(R.id.test_sqlite_showCounter);
		
		create.setOnClickListener(listener_create);
		insert.setOnClickListener(listener_insert);
		drop.setOnClickListener(listener_drop);
		delete.setOnClickListener(listener_delete);
		counter.setOnClickListener(listener_counter);
		
	}
	
	private void deleteData(){
		try {
			SQLiteDatabase db = mOpenHelper.getWritableDatabase();
			db.delete(TABLE_NAME, " title = 'aloxc'", null);
			setTitle("删除title为haiyang的一条记录");
		} catch (SQLException e) {

		}
	}
	
	private void insertData(){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String sql1 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
				+ ") values('aloxc', 'android的发展真是迅速啊');";
		String sql2 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY
				+ ") values('leerohwa', 'android的发展真是迅速啊');";
		try {
			db.execSQL(sql1);
			db.execSQL(sql2);
			setTitle("插入两条数据成功");
		} catch (SQLException e) {
			setTitle("插入两条数据失败");
		}
	}
	private void dropTable(){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String sql = "drop table " + TABLE_NAME;
		try {
			db.execSQL(sql);
			setTitle("数据表成功删除：" + sql);
		} catch (SQLException e) {
			setTitle("数据表删除错误");
		}
	}
	private void createTable(){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		String sql = "CREATE TABLE " + TABLE_NAME + " (" + TITLE
				+ " text not null, " + BODY + " text not null " + ");";
		try {
			db.execSQL("DROP TABLE IF EXISTS diary");
			db.execSQL(sql);
			setTitle("数据表成功重建");
		} catch (SQLException e) {
			setTitle("数据表重建错误");
		}
	}
	/*
	 * 在屏幕的title区域显示当前数据表当中的数据的条数。
	 */
	private void showItems() {

		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		String col[] = { TITLE, BODY };
		Cursor cur = db.query(TABLE_NAME, col, null, null, null, null, null);
		Integer num = cur.getCount();
		setTitle(Integer.toString(num) + " 条记录");
	}
	private class DbHelper extends SQLiteOpenHelper{

		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			String sql = "CREATE TABLE " + TABLE_NAME + " (" + TITLE
			+ " text not null, " + BODY + " text not null " + ");";
			db.execSQL(sql);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			
		}
		
	}
}
